package com.project.dao.imp;


import com.project.bean.FacilityBean;
import com.project.bean.RoomBean;

import com.project.dao.IFacilityDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class IFacilityDaoImp extends BaseDao implements IFacilityDao {
    @Override
    public List<FacilityBean> findById(Integer id) {
        List<FacilityBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareCall("select * from r_facility,r_room where fk_roomId = pk_roomId and fk_roomId = ? ");
            ps.setObject(1, id);

            re = ps.executeQuery();
            while (re.next()) {
                FacilityBean facilityBean = new FacilityBean();
                facilityBean.setId(re.getInt("pk_acilityId"));
                facilityBean.setDescribe(re.getString("r_describe"));
                facilityBean.setBrokenDate(LocalDate.parse(re.getString("r_brokenDate")));
                facilityBean.setOver(re.getString("r_over"));
                RoomBean roomBean = new RoomBean();
                roomBean.setId(re.getInt("fk_roomId"));
                list.add(facilityBean);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return list;
    }

    @Override
    public void add(FacilityBean facilityBean) {
        this.setConnection();
        try {
            ps = con.prepareCall("INSERT INTO r_facility(r_describe,r_brokenDate,r_over,fk_roomId)" +
                    "VALUES(?,?,?,?)");
            ps.setObject(1, facilityBean.getDescribe());
            ps.setObject(2, facilityBean.getBrokenDate());
            ps.setObject(3, facilityBean.getOver());
            ps.setObject(4, facilityBean.getRoomBean().getId());

            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public void updateStatus(Integer id, String status) {
        this.setConnection();
        try {
            ps = con.prepareCall("update r_facility set r_over = ? where pk_acilityId = ?");
            ps.setObject(1, status);
            ps.setObject(2, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public int findFacilitiesNum(Integer roomId, String status) {
        int num = 0;
        this.setConnection();
        try {
            ps = con.prepareCall("SELECT COUNT(r_over) c FROM r_room,r_facility WHERE r_state = ? AND pk_roomId = fk_roomId AND pk_roomId =? ");
            ps.setObject(1, status);
            ps.setObject(2, roomId);


            re = ps.executeQuery();
            if (re.next()) {
               num = re.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return num;
    }

    @Override
    public void del(Integer roomId) {
        this.setConnection();
        try {
            ps = con.prepareCall("DELETE from r_facility WHERE pk_acilityId = (SELECT pk_roomId FROM r_room WHERE pk_roomId = ?)");
            ps.setObject(1, roomId);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }
}
